﻿/* Author : TonyChen
 * Date : 10/3/2012
 * function DataUtil
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Collections;
using System.IO;
using System.Configuration;
using common;
namespace util
{
    public class DataUtil
    {
        protected static MySqlConnection conn;

        /* Author : TonyChen
         * Date : 10/3/2012
         * function connect
         * connect to mysql server
         * return
         */
        public static Boolean connect()
        {
            String connectionString = "Server=" + Constant.SERVER + ";Database="
                                        + Constant.DATABASE + ";uid="
                                            + ConfigurationManager.AppSettings["uid"] + ";password="
                                                + ConfigurationManager.AppSettings["PASSWORD"] + ";"
                                                    + ConfigurationManager.AppSettings["charset"];
            try
            { 
                conn = new MySqlConnection(connectionString);
                conn.Open();
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
            return true;
        }

        /* Author : TonyChen
         * Date : 10/3/2012
         * function getConnection
         * get connect on system
         * no return
         */
        public static MySqlConnection getConnection()
        {
            if (conn == null)
                connect();
            return conn;
        }

        /* Author : TonyChen
         * Date : 10/3/2012
         * function disconnect
         * disconnect system
         * no return
         */
        public static void disconnect()
        {
            conn.Close();
        }

        /* Author : TonyChen
         * Date : 10/3/2012
         * function executeStore
         * execute storeprocedure
         * return
         */
        public static DataTable executeStore(string spName, MySqlParameter[] arrParam)
        {
            DataSet ds = new DataSet();
            if (connect())
            {
                try
                {
                    MySqlDataAdapter adapter = new MySqlDataAdapter();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = spName;
                    cmd.CommandTimeout = 1000;

                    if (arrParam != null)
                    {
                        foreach (MySqlParameter param in arrParam)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    adapter.SelectCommand = cmd;
                    adapter.Fill(ds);
                }
                catch (Exception ex)
                {
                    //log.Error(ex.Message, ex);
                    Console.WriteLine(ex.Message);
                    return null;
                }
            }
            return ds.Tables[0];
        }

        /* Author : TonyChen
         * Date : 10/3/2012
         * function executeNonQuery
         * execute query
         * return
         */
        public static Boolean executeNonQuery(string spName, MySqlParameter[] arrParam)
        {
            if (connect())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = spName;
                    if (arrParam != null)
                    {
                        foreach (MySqlParameter param in arrParam)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    cmd.CommandTimeout = 1000;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    //log.Error(ex.Message, ex);
                    Console.WriteLine(ex.Message);
                    return false;
                }
            }
            return true;
        }

        public static MySqlDataReader executeReader(string spName, MySqlParameter[] arrParam)
        {
            MySqlDataReader reader;
            try
            {
                MySqlCommand command = new MySqlCommand(spName, conn);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = 1000;
                if (arrParam != null)
                {
                    foreach (MySqlParameter param in arrParam)
                    {
                        command.Parameters.Add(param);
                    }
                }
                reader = command.ExecuteReader();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                return null;
            }
            return reader;
        }

        public static Boolean doActionByMysqlDataAdapter(String actionType,MySqlDataAdapter adapter, DataTable dt, String cmdText, MySqlParameter[] arrpam)
        {
            try
            {
                MySqlCommand cmd = new MySqlCommand(cmdText, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                if (arrpam != null)
                {
                    for (int i = 0; i < arrpam.Length; i++)
                    {
                        cmd.Parameters.Add(arrpam[i]);
                    }
                }
                if (actionType.Equals("1"))
                {
                    adapter.InsertCommand = cmd;
                    adapter.InsertCommand.ExecuteNonQuery();
                }
                else if (actionType.Equals("2"))
                {
                    adapter.UpdateCommand = cmd;
                    adapter.UpdateCommand.ExecuteNonQuery();
                }
                else
                {
                    adapter.DeleteCommand = cmd;
                    adapter.DeleteCommand.ExecuteNonQuery();
                }
                
                if (adapter.Update(dt) == 1)
                    return true;
                else return false;
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                return false;
            }
        }
        public static MySqlDataAdapter executeStoreReturnAdapter(string spName, MySqlParameter[] arrParam)
        {
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            if (connect())
            {
                try
                {
                    
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Connection = conn;
                    cmd.CommandText = spName;
                    cmd.CommandTimeout = 1000;

                    if (arrParam != null)
                    {
                        foreach (MySqlParameter param in arrParam)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    adapter.SelectCommand = cmd;
                }
                catch (Exception ex)
                {
                    //log.Error(ex.Message, ex);
                    Console.WriteLine(ex.Message);
                    return null;
                }
            }
            return adapter;
        }
        /*
        public static Boolean executeMultiNonStore(List<QueryModel> _ListQuery)
        {
            MySqlCommand cmd = null;
            if (connect())
            {
                try
                {
                    trans = conn.BeginTransaction();
                    foreach (QueryModel _Query in _ListQuery)
                    {
                        cmd = new MySqlCommand();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Connection = conn;
                        cmd.CommandText = _Query.SpName;
                        cmd.Transaction = trans;
                        cmd.CommandTimeout = 500;
                        if (_Query.ArrParam != null)
                        {
                            foreach (MySqlParameter param in _Query.ArrParam)
                            {
                                cmd.Parameters.Add(param);
                            }
                        }
                        cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    if (trans != null)
                        trans.Rollback();
                    Console.WriteLine(ex.Message);
                    return false;
                }
                finally
                {
                    conn.Close();
                }
            }
            return true;
        }
         */
    }
}
